A Framework for Re-optimizing Repetitive Queries

In this paper, we develop a comprehensive framework forre-optimization of a large and useful set of queries, called repetitive queries. Repetitive queries refer tothosequeries that are likely to be used repeatedly or frequently in the future. Theydeserve more optimization efforts than ordinary ad hoc queries. In this research, weidentify statistics, called sufficient statistics,that are sufficient to compute the exact frequency distributions of the intermediate results of all plans ofa query. We present two innovative techniques to conductreoptimization, an eager and a lazy re-optimization. The eager approach gathersall the sufficient statistics for a query at once and generates the best plan. The lazy reoptimization gathers only the statistics that are needed to correctlarge estimation errors found in the plan and generates a revised plan. We further adapt the two basictechniques to constantly changing environmentsby continuously monitoring and revising the plans, called adaptive re-optimization. The adaptive re-optimization is devised to detectand remedy potential sub-optimality in the plans in a timely manner for the entire lifetime of the query. Our work realizes the promise made by the query optimizers, namely, executing queries in the optimal fashions, at least for the repetitive queries.